package cn.boltzjf.sql4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Objects;
import java.util.stream.Collectors;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class Sql {
    
    private static final Logger log = LoggerFactory.getLogger(Sql.class);
    
    public static class Param {
        private Object value;
        private String sqlPlaceholder;
        
        public Param() {}
        
        public Param(Object value) {
            this.value = value;
        }
        
        public Param(Object value, String sqlPlaceholder) {
            this.value = value;
            this.sqlPlaceholder = sqlPlaceholder;
        }
        
        public Object getValue() {
            return value;
        }

        public void setValue(Object value) {
            this.value = value;
        }

        public String getSqlPlaceholder() {
            return sqlPlaceholder;
        }

        public void setSqlPlaceholder(String sqlPlaceholder) {
            this.sqlPlaceholder = sqlPlaceholder;
        }

        @Override
        public String toString() {
            return String.format("Param{value: %s, sqlPlaceholder: %s}", value, sqlPlaceholder);
        }
    }
    
    private final StringBuilder sqlBf = new StringBuilder();
    private final Map<String, Object> nameMap = new HashMap<>();
    private final Map<String, Param> paramMap = new HashMap<>();
    
    public Sql() {}
    
    public Sql(CharSequence sql) {
        sqlBf.append(sql);
    }
    
    public Sql append(CharSequence sql) {
        sqlBf.append(sql);
        return this;
    }
    
    public Sql append(String delimiter, String prefix, Object... objs) {
        if (objs != null && objs.length > 0) {
            for (Object obj : objs) {
                if (prefix != null) {
                    sqlBf.append(prefix);
                }
                sqlBf.append(obj);
                if(delimiter != null) {
                    sqlBf.append(delimiter);
                }
            }
            if (delimiter != null) {
                deleteLastCharsInSql(delimiter.length());
            }
        }
        return this;
    }
    
    public Sql append(String delimiter, String prefix, Collection<?> objs) {
        if (objs != null && !objs.isEmpty()) {
            for (Object obj : objs) {
                if (prefix != null) {
                    sqlBf.append(prefix);
                }
                sqlBf.append(obj);
                if(delimiter != null) {
                    sqlBf.append(delimiter);
                }
            }
            if (delimiter != null) {
                deleteLastCharsInSql(delimiter.length());
            }
        }
        return this;
    }
    
    public Sql append(String prefix, Object... objs) {
        return append(",", prefix, objs);
    }
    
    public Sql append(String prefix, Collection<?> objs) {
        return append(",", prefix, objs);
    }
    
    public Sql append(Object... objs) {
        return append(",", null, objs);
    }
    
    public Sql append(Collection<?> objs) {
        return append(",", null, objs);
    }
    
    public Sql deleteLastCharsInSql(int count) {
        count = Math.min(sqlBf.length(), count);
        if (count > 0) {
            sqlBf.delete(sqlBf.length() - count, sqlBf.length());
        }
        return this;
    }
    
    public Sql deleteLastCharInSql() {
        return deleteLastCharsInSql(1);
    }
    
    public Sql mapName(String namePlaceholder, Object name) {
        nameMap.put(namePlaceholder, name);
        return this;
    }
    
    public Sql mapParam(String paramPlaceholder, Object param) {
        return mapParam(paramPlaceholder, param, null);
    }
    
    public Sql mapParam(String paramPlaceholder, Object param, String sqlPlaceholder) {
        paramMap.put(paramPlaceholder, new Param(param, sqlPlaceholder));
        return this;
    }
    
    public Sql mapNameAndParam(String placeholder, Object name, Object param) {
        return mapNameAndParam(placeholder, name, param, null);
    }
    
    public Sql mapNameAndParam(String placeholder, Object name, Object param, String sqlPlaceholder) {
        nameMap.put(placeholder, name);
        paramMap.put(placeholder, new Param(param, sqlPlaceholder));
        return this;
    }
    
    private int number = 0;
    private static final String PARAM_NAME_TEMPLATE = "__p_%d";
    
    private String newParamName() {
        return String.format(PARAM_NAME_TEMPLATE, (++number));
    }
    
    public Sql appendParam(Object param, String sqlPlaceholder) {
        String paramName = newParamName();
        sqlBf.append("?{").append(paramName).append("}");
        paramMap.put(paramName, new Param(param, sqlPlaceholder));
        return this;
    }
    
    public Sql appendParam(Object param) {
        if (Param.class.isInstance(param)) {
            Param p = (Param) param;
            return appendParam(p.getValue(), p.getSqlPlaceholder());
        } else {
            return appendParam(param, null);
        }
    }
    
    public Sql appendParam(Param param) {
        return appendParam((Object)param);
    }
    
    public Sql appendParams(String delimiter, List<Object> params) {
        if (params != null && !params.isEmpty()) {
            for (Object obj : params) {
                if (Param.class.isInstance(obj)) {
                    Param param = (Param) obj;
                    appendParam(param.getValue(), param.getSqlPlaceholder()).append(delimiter);
                }
            }
            deleteLastCharsInSql(delimiter.length());
        }
        return this;
    }
    
    public Sql appendParams(List<Object> params) {
        return appendParams(",", params);
    }
    
    public String toSql() {
        return toSqlWithParamMap(paramMap);
    }
    
    private String toSqlWithParamMap(Map<String, Param> paramMap) {
    	StringBuilder bf = new StringBuilder();
        fillNamesAndParams(sqlBf, nameMap, paramMap, bf);
        return bf.toString();
    }
    
    public List<Object> getParams() {
        return getParamsFromParamMap(paramMap);
    }
    
    private List<Object> getParamsFromParamMap(Map<String, Param> inputParamMap) {
    	List<Object> params = new ArrayList<>();
        fillParams(sqlBf, inputParamMap, params);
        return params;
    }
    
    public <T> List<T> executeQuery(Connection conn, ResultConverter<T> converter) {
        String sql = toSql();
        List<Object> params = getParams();
        log.debug("SQL: [{}], Params: {}", sql, params);
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            setParameters(ps, params);
            try (ResultSet rs = ps.executeQuery()) {
                return converter.convert(rs);
            }
        } catch (SQLException e) {
            throw new SqlException("Failed to execute query: " + e.getMessage(), e);
        }
    }

    public int executeUpdate(Connection conn) {
        String sql = toSql();
        List<Object> params = getParams();
        log.debug("SQL: [{}], Params: {}", sql, params);
        
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            setParameters(ps, params);
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new SqlException("Failed to execute update: " + e.getMessage(), e);
        }
    }
    
    public int[] executeBatchUpdate(Connection conn, List<Map<String, Object>> paramsList) {
    	ensure(paramsList != null && !paramsList.isEmpty(), "Parameters map list should not be null or empty");
    	paramsList.forEach(map -> ensure(map != null && !map.isEmpty(), "Any parameters map in the list should not be null or empty"));
    	Map<String, Object> firstParamMap = paramsList.get(0);
    	paramsList.forEach(map -> ensure(Objects.equals(map.keySet(), firstParamMap.keySet()), "All parameters map should have the same keys"));
    	
    	List<Map<String, Param>> standardizedParamMapList = paramsList.stream().map(paramMap -> standardize(paramMap)).collect(Collectors.toList());
    	Map<String, Param> firstStandardizedParamMap = standardizedParamMapList.get(0);
		String sql = toSqlWithParamMap(firstStandardizedParamMap);
		
		try (PreparedStatement ps = conn.prepareStatement(sql)) {
			for (Map<String, Param> paramMap : standardizedParamMapList) {
				List<Object> params = getParamsFromParamMap(paramMap);
				setParameters(ps, params);
				ps.addBatch();
			}
			return ps.executeBatch();
		} catch (SQLException e) {
            throw new SqlException("Failed to execute update: " + e.getMessage(), e);
        }
    }
    
    private Map<String, Param> standardize(Map<String, Object> params) {
    	Map<String, Param> map = null;
    	if (params != null) {
    		map = new HashMap<>();
    		for (Iterator<Entry<String, Object>> it = params.entrySet().iterator(); it.hasNext();) {
    			Entry<String, Object> entry = it.next();
    			Object value = entry.getValue();
    			if (Param.class.isInstance(value)) {
    				map.put(entry.getKey(), (Param) value);
    			} else {
    				map.put(entry.getKey(), new Param(value, null));
    			}
    		}
    	}
    	return map;
    }
    
    private void setParameters(PreparedStatement ps, List<Object> params) throws SQLException {
        if (params != null && !params.isEmpty()) {
            int i = 0;
            for (Object param : params) {
                ps.setObject(++i, param);
            }
        }
    }
    
    private void ensure(boolean expected, String errorMessage, Object... errorParams) {
    	if (!expected) {
    		throw new IllegalArgumentException(errorParams != null && errorParams.length > 0 ? String.format(errorMessage, errorParams) : errorMessage);
    	}
    }
    
    private void fillNamesAndParams(StringBuilder sql, Map<String, Object> nameMap, Map<String, Param> paramMap, StringBuilder bf) {
        for (int i = 0; i < sql.length();) {
            char c = sql.charAt(i);
            if (c == '#') {
                if (i + 3 < sql.length() && sql.charAt(i + 1) == '{') {
                    int close = sql.indexOf("}", i + 2);
                    if (close >= 0) {
                        String key = sql.substring(i + 2, close);
                        Object name = nameMap.get(key);
                        if (name == null) {
                            throw new IllegalArgumentException(String.format("Cannot find name for placeholder #{%s} in sql: %s", key, sql));
                        }
                        if (Collection.class.isInstance(name)) {
                            Collection<?> names = (Collection<?>) name;
                            if (names.isEmpty()) {
                                throw new IllegalArgumentException(String.format("Name collection for placeholder #{%s} in sql is empty", key));
                            }
                            for (Object n : names) {
                                bf.append(n.toString()).append(',');
                            }
                            bf.deleteCharAt(bf.length() - 1);
                        } else if (name.getClass().isArray()) {
                            Object[] names = (Object[]) name;
                            if (names.length == 0) {
                                throw new IllegalArgumentException(String.format("Name array for placeholder #{%s} in sql is empty", key));
                            }
                            for (Object n : names) {
                                bf.append(n.toString()).append(',');
                            }
                            bf.deleteCharAt(bf.length() - 1);
                        } else { // simple name
                            bf.append(name.toString());
                        }
                        i = close + 1;
                        continue;
                    }
                }
            } else if (c == '?') {
                if (i + 3 < sql.length() && sql.charAt(i + 1) == '{') {
                    int close = sql.indexOf("}", i + 2);
                    if (close >= 0) {
                        String key = sql.substring(i + 2, close);
                        Param param = paramMap.get(key);
                        if (param == null) {
                            throw new IllegalArgumentException(String.format("Cannot find param for placeholder ?{%s} in sql: %s", key, sql));
                        }
                        Object value = param.getValue();
                        if (value != null) {
                            if (Collection.class.isInstance(value)) {
                                Collection<?> values = (Collection<?>) value;
                                if (!values.isEmpty()) {
                                    for (Object v : values) {
                                        String sqlPlaceholder = Param.class.isInstance(v) ? ((Param)v).getSqlPlaceholder() : param.getSqlPlaceholder();
                                        bf.append(sqlPlaceholder != null ? sqlPlaceholder : "?").append(",");
                                    }
                                    bf.deleteCharAt(bf.length() - 1);
                                }
                            } else if (value.getClass().isArray() && Object[].class.isInstance(value)) {
                                Object[] values = (Object[]) value;
                                if (values.length > 0) {
                                    for (Object v : values) {
                                        String sqlPlaceholder = Param.class.isInstance(v) ? ((Param)v).getSqlPlaceholder() : param.getSqlPlaceholder();
                                        bf.append(sqlPlaceholder != null ? sqlPlaceholder : "?").append(",");
                                    }
                                    bf.deleteCharAt(bf.length() - 1);
                                }
                            } else { // simple value
                                bf.append(param.getSqlPlaceholder() != null ? param.getSqlPlaceholder() : "?");
                            }
                        } else { // value == null
                            bf.append(param.getSqlPlaceholder() != null ? param.getSqlPlaceholder() : "?");
                        }
                        i = close + 1;
                        continue;
                    }
                }
            }
            bf.append(c);
            ++i;
        }
    }
    
    private void fillParams(StringBuilder sql, Map<String, Param> paramMap, List<Object> params) {
        for (int i = 0; i < sql.length();) {
            char c = sql.charAt(i);
            if (c == '?') {
                if (i + 3 < sql.length() && sql.charAt(i + 1) == '{') {
                    int close = sql.indexOf("}", i + 2);
                    if (close >= 0) {
                        String key = sql.substring(i + 2, close);
                        Param param = paramMap.get(key);
                        Object value = param.getValue();
                        if (value != null) {
                            if (Collection.class.isInstance(value)) {
                                Collection<?> values = (Collection<?>) value;
                                if (!values.isEmpty()) {
                                    for (Object v : values) {
                                        Object p = Param.class.isInstance(v) ? ((Param)v).getValue() : v;
                                        params.add(p);
                                    }
                                }
                            } else if (value.getClass().isArray() && Object[].class.isInstance(value)) {
                                Object[] values = (Object[]) value;
                                if (values.length > 0) {
                                    for (Object v : values) {
                                        Object p = Param.class.isInstance(v) ? ((Param)v).getValue() : v;
                                        params.add(p);
                                    }
                                }
                            } else { // simple value
                                params.add(value);
                            }
                        } else { // value == null
                            params.add(value);
                        }
                    }
                }
            }
            ++i;
        }
    }
}
